package com.sztz.utils;

import cn.hutool.core.exceptions.ExceptionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.sztz.constants.CommonConstants;
import com.sztz.entity.WxUser;
import com.sztz.excel.ExcelVoListener;
import com.sztz.result.ResultEnum;
import com.sztz.result.ServiceException;
import com.sztz.vo.TradeDetailsExcelVo;
import com.sztz.vo.TradeDetailsVo;
import com.sztz.vo.WxChargeAccountExcelVo;
import lombok.extern.slf4j.Slf4j;

import java.io.BufferedInputStream;
import java.io.File;
import java.math.BigDecimal;
import java.util.*;
import java.util.function.Consumer;
import java.util.stream.Collectors;

/**
 * EXCEL处理小程序工具类
 * @author yzy
 * @date 2022/11/29 14:19
 */
@Slf4j
public class ExcelUtils {
    /**
     * Excel文件基础路径
     */
    private static final String BASE_EXCEL_PATH="D:\\wx\\20221129\\";
    /**
     * 读取EXCEL文件，获取注册超过30天的微信小程序个人用户
     * @return insert语名
     * @author yzy
     * @date 2022/11/29 14:19
     */
    public static List<WxUser> readWxUserExcel(){
        return EasyExcelFactory.read(new File(BASE_EXCEL_PATH+"wxUser.xlsx")).head(WxUser.class).sheet().doReadSync();
    }
    /**
     * 读取EXCEL文件，获取做过单据的微信小程序
     * @return insert语名
     * @author yzy
     * @date 2022/11/30 18:57
     */
    public static List<WxUser> readHasOrderUser(){
        return EasyExcelFactory.read(new File(BASE_EXCEL_PATH+"1129_1.xlsx")).head(WxUser.class).sheet().doReadSync();
    }
    /**
     * 读取EXCEL文件，获取注册超过30天的微信小程序个人用户计费账号
     * @return insert语名
     * @author yzy
     * @date 2022/11/29 14:19
     */
    public static List<WxChargeAccountExcelVo> readWxChargeAccountUser(){
        return EasyExcelFactory.read(new File(BASE_EXCEL_PATH+"chargeAccount.xlsx")).head(WxChargeAccountExcelVo.class).sheet().doReadSync();
    }

    public static void main(String[] args) {
        writerFreeCellNumeric();
    }
    /**
     * 导出Excel设置单元格数值类型，每一列都相同
     * @return insert语名
     * @author yzy
     * @date 2022/01/07 14:19
     */
    public static void writerFreeCellType (){
        List<TradeDetailsVo> freeTradeList = getFreeTradeList();
        String outFilePath= StrUtil.concat(Boolean.TRUE,BASE_EXCEL_PATH,"outsql\\free_num.xlsx");
        List<String> cellDataTypes = Arrays.asList("GENERAL","GENERAL","GENERAL","GENERAL","DOUBLE","DOUBLE","GENERAL","INTEGER");
        ExcelWriterBuilder excelWriterBuilder = EasyExcelUtils.getExcelWriterByCellType(FileUtil.file(outFilePath), TradeDetailsVo.class, cellDataTypes);
        excelWriterBuilder.sheet().doWrite(freeTradeList);
    }
    /**
     * 导出Excel跟据行号，列号指定单元格符点数类型
     * @author yzy
     * @date 2022/01/07 14:19
     */
    public static void writerFreeCellNumeric (){
        List<TradeDetailsVo> freeTradeList = getFreeTradeList();
        String outFilePath= StrUtil.concat(Boolean.TRUE,BASE_EXCEL_PATH,"outsql\\free_num.xlsx");


        Map<String,List<Integer>> columnIndexMap = new HashMap<>();
        Map<String,List<String>> rowIndexMap = new HashMap<>();
        //第四列跟第五列符点数
        List<Integer> doubleColumnList = Arrays.asList(4,5);
        columnIndexMap.put(CommonConstants.CELL_STYLE_KEY_DOUBLE,doubleColumnList);
        //去掉表头，从下标零开始，第三行以及第五到第九行设为符点数
        List<String> doubleRowList = Arrays.asList("3-3","9-5");
        rowIndexMap.put(CommonConstants.CELL_STYLE_KEY_DOUBLE,doubleRowList);
        //第四列跟第五列符点数
        List<Integer> integerColumnList = Arrays.asList(1,6,7);
        columnIndexMap.put(CommonConstants.CELL_STYLE_KEY_INTEGER,integerColumnList);
        //去掉表头，从下标零开始，第三行以及第五到第九行设为符点数
        List<String> integerRowList = Arrays.asList("1-4","20-35");
        rowIndexMap.put(CommonConstants.CELL_STYLE_KEY_INTEGER,integerRowList);
        ExcelWriterBuilder excelWriterBuilder = EasyExcelUtils.getExcelWriterForCellFmt(FileUtil.file(outFilePath), TradeDetailsVo.class, columnIndexMap, rowIndexMap);
        excelWriterBuilder.sheet().doWrite(freeTradeList);
    }
    
    /**
     * 导出只用过免单的小程序个人用户
     * @author yzy
     * @date 2022/12/01 14:19
     */
    public static void downOnlyFreeWxUserExcel(){
        //获取只用过免单的微信小程序个人用户计费账户
        Set<String> strings = readWxTrade();
        //读取出注册超过30天的微信小程序个人用户计费账户信息
        List<WxChargeAccountExcelVo> wxChargeAccountExcelVos = readWxChargeAccountUser();
        //筛选出只用了免单的小程序个人计费账号
        List<WxChargeAccountExcelVo> collect = strings.stream().filter(item -> wxChargeAccountExcelVos.stream().anyMatch(wx -> StrUtil.equalsAnyIgnoreCase(wx.getAccountId(), item))
        ).map(item -> wxChargeAccountExcelVos.stream().filter(wx -> StrUtil.equalsAnyIgnoreCase(wx.getAccountId(), item)).findAny().get()).collect(Collectors.toList());
        log.info("wx only free charge= {}",collect.size());
        //获取注册超过30天的微信小程序个人用户信息
        List<WxUser>  wxUsers= readWxUserExcel();
        List<WxUser> collect1 = collect.stream().filter(item -> wxUsers.stream().anyMatch(wxUser -> StrUtil.equalsAnyIgnoreCase(wxUser.getLoginCode(), item.getLoginCode())))
                .map(item -> wxUsers.stream().filter(wxUser -> StrUtil.equalsAnyIgnoreCase(wxUser.getLoginCode(), item.getLoginCode())).findAny().get()).collect(Collectors.toList());
        //导出只用了免单的小程序个人用户信息
        log.info("wx only free user info= {}",collect1.size());
        String outFilePath= StrUtil.concat(Boolean.TRUE,BASE_EXCEL_PATH,"outsql\\wx_only_free.xlsx");
        EasyExcelFactory.write(FileUtil.file(outFilePath),WxUser.class).sheet().doWrite(collect1);
    }
    /**
     * 读取EXCEL文件，获取使用免单的微信小程序个人用户计费账号
     * @return insert语名
     * @author yzy
     * @date 2022/12/01 14:19
     */
    private static List<TradeDetailsVo> getFreeTradeList(){
        //没有下过订单的账号集合
        List<TradeDetailsVo> freeTradeList = new ArrayList<>();
        File file = FileUtil.file(BASE_EXCEL_PATH+"free1.xlsx");
        try(BufferedInputStream inputStream = FileUtil.getInputStream(file)){
            Consumer<List<TradeDetailsVo>> userConsumer = data->{
                //处理数据
                freeTradeList.addAll(data);
            };
            EasyExcelFactory.read(inputStream, TradeDetailsVo.class, new ExcelVoListener<>(userConsumer)).doReadAll();
        }catch (Exception e){
            log.error(ExceptionUtil.stacktraceToString(e));
            throw new ServiceException(ResultEnum.UNKNOWN_ERROR);
        }
        return freeTradeList;
    }

    /**
     * 读取EXCEL文件，导出没有下过单的微信小程序个人用户
     * @return insert语名
     * @author yzy
     * @date 2022/12/01 14:19
     */
    private static void writeNoOrderUserExcel() {
        //获取注册超过30天的微信小程序个人用户信息
        List<WxUser> wxUsers = readWxUserExcel();
        //获取做过单据的微信小程序个人用户信息
        List<WxUser> hasOrderUser = readHasOrderUser();
        List<WxUser> collect = wxUsers.stream()
                .filter(item -> hasOrderUser.stream().noneMatch(order -> StrUtil.equalsAnyIgnoreCase(item.getId(), order.getId())))
                .collect(Collectors.toList());
        log.info("size={}",collect.size());
        String outFilePath= StrUtil.concat(Boolean.TRUE,BASE_EXCEL_PATH,"outsql\\wx_no_order_user.xlsx");
        EasyExcelFactory.write(FileUtil.file(outFilePath),WxUser.class).sheet().doWrite(collect);
    }

    /**
     * 处理交易数据
     * @return java.util.Set<java.lang.String>
     * @author yzy
     * @date 2022/12/2 10:02
     */
    public static Set<String> readWxTrade(){
        //用过免单的用户
        Set<String> freePayAccount = getFreePayAccount();
        log.info("freePayAccount = {}",freePayAccount.size());
        //有下过订单的账号集合
        Set<String> hasOrderAccountSet = new HashSet<>();
        //充值账户
        Map<String,BigDecimal> reChargeMap =new HashMap<>(8);
        //退费账户
        Map<String,BigDecimal> refundMap =new HashMap<>(8);

        Map<String ,TradeDetailsExcelVo> map = new HashMap<>(8);
        File file = FileUtil.file(BASE_EXCEL_PATH+"wxtd.xlsx");
        try(BufferedInputStream inputStream = FileUtil.getInputStream(file)){
            Consumer<List<TradeDetailsExcelVo>> userConsumer = data->{
                //处理数据
                data.forEach(item->{
                    if(item.getFeeType()==3){
                        return ;
                    }
                    //处理实际付过款的账号
                    if(hasOrderAccountSet.contains(item.getAccountId())){
                        return ;
                    }

                    //如果是实际付过款的账号存到set中
                    if(hasPay(item)){
                        hasOrderAccountSet.add(item.getAccountId());
                        if(map.containsKey(item.getAccountId())){
                            map.remove(item.getAccountId());
                        }
                        if(freePayAccount.contains(item.getAccountId())){
                            freePayAccount.remove(item.getAccountId());
                        }
                    }else{
                        //普通计费
                        if(item.getFeeType()==0){
                            switch (item.getTradeType()){
                                case 0:
                                    reChargeMap.put(item.getAccountId(),reChargeMap.getOrDefault(item.getAccountId(),BigDecimal.ZERO).add(item.getTradeAmount()));
                                    break;
                                case 1:
                                    break;
                                case 2:
                                    refundMap.put(item.getAccountId(),refundMap.getOrDefault(item.getAccountId(),BigDecimal.ZERO).add(item.getTradeAmount()));
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                });
            };
            EasyExcelFactory.read(inputStream, TradeDetailsExcelVo.class, new ExcelVoListener<>(userConsumer)).doReadAll();
        }catch (Exception e){
            log.error(ExceptionUtil.stacktraceToString(e));
            throw new ServiceException(ResultEnum.UNKNOWN_ERROR);
        }
        log.info("hasOrderAccountSet = {}",hasOrderAccountSet.size());
        log.info("freePayAccountAndNoPayOrder = {}",freePayAccount.size());
        return freePayAccount;
    }
    /**
     * 读取EXCEL文件，获取使用免单的微信小程序个人用户计费账号
     * @return insert语名
     * @author yzy
     * @date 2022/12/01 14:19
     */
    private static Set<String> getFreePayAccount(){
        //没有下过订单的账号集合
        Set<String> freeAccountSet = new HashSet<>();
        File file = FileUtil.file(BASE_EXCEL_PATH+"free.xlsx");
        try(BufferedInputStream inputStream = FileUtil.getInputStream(file)){
            Consumer<List<TradeDetailsExcelVo>> userConsumer = data->{
                //处理数据
                data.forEach(item->{
                    freeAccountSet.add(item.getAccountId());
                });
            };
            EasyExcelFactory.read(inputStream, TradeDetailsExcelVo.class, new ExcelVoListener<>(userConsumer)).doReadAll();
        }catch (Exception e){
            log.error(ExceptionUtil.stacktraceToString(e));
            throw new ServiceException(ResultEnum.UNKNOWN_ERROR);
        }
        return freeAccountSet;
    }

    /**
     * 是否真实付过款
     * @param tradeDetailsExcelVo 交易记录
     * @return boolean
     * @author yzy
     * @date 2022/12/2 9:59
     */
    private static boolean hasPay(TradeDetailsExcelVo tradeDetailsExcelVo){
        //交易方式是付款，并且交易金额大于0，计费方式是普通计费 或 南电套餐计费
        return tradeDetailsExcelVo.getTradeType()==1
                && tradeDetailsExcelVo.getTradeAmount().compareTo(BigDecimal.ZERO)>0
                && (tradeDetailsExcelVo.getFeeType()==0 || tradeDetailsExcelVo.getFeeType()==2);
    }
}
